# 更新数据库，产品目录
import pandas as pd
from bs4 import BeautifulSoup
import grequests
import re
from sqlalchemy import create_engine


# 链接连接mysql
# 更新数据库
global conn
conn = create_engine("mysql+pymysql://yinhang:yinghang@123@49.234.83.104:3306/apple_mac?charset=utf8")

def update_product():

    sql_str = "SELECT * FROM apple_mac"
    all_p_No = pd.read_sql(sql=sql_str, con=conn)
    # 请求头
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/21.0.1180.71 Safari/537.1 LBBROWSER'}
    # 获取所有编号
    all_p_No = list(all_p_No['ID'])
    # 基本代码
    base_url = r'https://www.apple.com/cn/shop/product/{}/A'
    # 获取线程数
    # 对列表进行分组
    urls = list((base_url.format(u) for u in all_p_No))
    req = (grequests.get(url=u, headers=headers) for u in urls)
    resp = grequests.map(req, size=20)  # 10个并发
    for i in resp:
        if i.status_code == 200:
            # 判断是否缺货
            soup = BeautifulSoup(i.content, 'html')
            # 产品型号
            mac_code = re.search(r'\w{5}CH', i.url).group()
            ahtml = str(
                soup.select_one(
                    r'#product-details-form > div > div.form-submit-btn.grouped-button-left > span > button'))
            if not re.search('disable', ahtml):

                # 产品描述
                product_info = soup.select_one('#title > h1').text.replace('\n', '').lstrip().rstrip()
                # 价格
                mac_price = soup.select_one(
                    r'#purchase-info-primary > li.as-price.as-pdp-price > div > span > span').text.replace('\n',
                                                                                                           '').lstrip().rstrip()
                # 将产品信息分组
                # 获取产品名
                mac_name = re.search('(Mac\w+\s?\D*)', product_info).group()
                # 获取cpu型号
                mac_cpu = re.search('[i|m]\d', product_info).group()
                # 显示器尺寸
                mac_size = re.search('([0-9]{1,}[.][0-9])|([0-9]{1,})', product_info).group()
                # mac颜色
                mac_color = re.search('\w{1,}$', product_info).group()
                # 获取产品的详细参数
                detail_res = str(soup.select(
                    '#productInfoSection > div > div.as-productinfosection-panel.Overview-panel.row > div.as-productinfosection-mainpanel.column.large-9.small-12')[
                                     0])
                bhtml = BeautifulSoup(detail_res, 'html')
                result = bhtml.text.replace('\n', '').lstrip().rstrip()
                # 产品生产年份
                production_data = re.search('[0-9]{4}\s*年', result).group()
                # 获取产品详细信息
                # 获取内存
                mac_ram = re.search('[0-9]{1,2}GB', result).group()
                # 获取容量
                mac_rom = re.search('([0-9]{3}GB)|([0-9]{1,}[.]?[0-9]?TB)', result).group()
                # 获取显卡
                mac_Graphics = bhtml.find(class_='para-list as-pdp-lastparalist').text.replace('\n',
                                                                                               '').lstrip().rstrip()

                print(
                    mac_name + '   ' + production_data + '   ' + mac_cpu + '   ' + mac_size + '   ' + mac_color + '   ' + mac_ram + '   ' + mac_rom + '   ' + mac_Graphics + '   ' + mac_price)
                conn.execute(r'UPDATE apple_mac SET Y = "{}",价格="{}" WHERE ID = "{}"'.format('有货', mac_price, mac_code))
                conn.commit()
            else:
                conn.execute(
                    r'UPDATE apple_mac SET Y = "{}"  WHERE ID = "{}"'.format('缺货', mac_code))
                conn.commit()
